{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " 驱动器 C 中的卷是 OS\n",
      " 卷的序列号是 AE6F-00B3\n",
      "\n",
      " C:\\Users\\27420\\PycharmProjects\\训练\\gitee\\future-teddy\\teddy-B\\code\\data 的目录\n",
      "\n",
      "2021/11/14  09:48    <DIR>          .\n",
      "2021/11/14  09:48    <DIR>          ..\n",
      "2021/11/14  09:32           410,222 result2_1.xlsx\n",
      "2021/11/14  09:46            93,338 result2_2.xlsx\n",
      "2021/11/02  20:09           196,721 附件1.xlsx\n",
      "2021/11/02  20:09           575,770 附件2.xlsx\n",
      "2021/11/02  20:10            40,230 附件3.xlsx\n",
      "2021/11/02  20:11            22,801 附件4.xlsx\n",
      "               6 个文件      1,339,082 字节\n",
      "               2 个目录 112,168,083,456 可用字节\n"
     ]
    }
   ],
   "source": [
    "ls data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#导包\n",
    "\n",
    "import array\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import datetime\n",
    "import jieba\n",
    "import matplotlib\n",
    "import matplotlib.pyplot as plt\n",
    "matplotlib.rcParams['font.sans-serif'] = ['SimHei'] \n",
    "matplotlib.rcParams['font.family']='sans-serif'\n",
    "matplotlib.rcParams['axes.unicode_minus'] = False "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 任务 1.1\t附件 1 的产品通用名称存在不规范的情况。请按照复混肥料（掺混肥料归入这一类）、有机-无机复混肥料、有机肥料和床土调酸剂这 4 种类别对附件 1 进行规范化处理。请在报告中给出处理思路、过程及必要的结果，同时将完整的结果保存到文件“result1_1.xlsx”中。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_excel('data/附件1.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0               复混肥料\n",
       "17              掺混肥料\n",
       "22              有机肥料\n",
       "45         有机-无机复混肥料\n",
       "142            床土调酸剂\n",
       "144          稻苗床土调酸剂\n",
       "1269        \\n有机肥料\\n\n",
       "1289          有机肥料\\n\n",
       "1479           有机肥料 \n",
       "2085     有机无机   复混肥料\n",
       "2150    有机无机    复混肥料\n",
       "2199    有机-无机   复混肥料\n",
       "2626            掺混肥料\n",
       "2869       有机－无机复混肥料\n",
       "Name: 产品通用名称, dtype: object"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#查找产品通用名称的不同名\n",
    "login = data['产品通用名称'].drop_duplicates()\n",
    "login"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "#规范化\n",
    "data.loc[data.产品通用名称=='复混肥料','产品通用名称'] = '复混肥料'\n",
    "data.loc[data.产品通用名称=='稻苗床土调酸剂','产品通用名称'] = '床土调酸剂'\n",
    "data.loc[data.产品通用名称=='\\n有机肥料\\n','产品通用名称'] = '有机肥料'\n",
    "data.loc[data.产品通用名称=='有机肥料\\n','产品通用名称'] = '有机肥料'\n",
    "data.loc[data.产品通用名称=='有机肥料 ','产品通用名称'] = '有机肥料'\n",
    "data.loc[data.产品通用名称=='有机无机   复混肥料','产品通用名称'] = '有机-无机复混肥料'\n",
    "data.loc[data.产品通用名称=='有机无机    复混肥料','产品通用名称'] = '有机-无机复混肥料'\n",
    "data.loc[data.产品通用名称=='有机-无机   复混肥料','产品通用名称'] = '有机-无机复混肥料'\n",
    "data.loc[data.产品通用名称=='掺混肥料','产品通用名称'] = '复混肥料'\n",
    "data.loc[data.产品通用名称==' 掺混肥料','产品通用名称'] = '复混肥料'\n",
    "data.loc[data.产品通用名称=='有机－无机复混肥料','产品通用名称'] = '有机-无机复混肥料'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0           复混肥料\n",
       "22          有机肥料\n",
       "45     有机-无机复混肥料\n",
       "142        床土调酸剂\n",
       "Name: 产品通用名称, dtype: object"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "login = data['产品通用名称'].drop_duplicates()\n",
    "login"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "data.to_excel('data/result1_1.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 计算附件 1 中各肥料产品的氮、磷、钾养分百分比之和，称为总无机养分百分比。请在报告中给出处理思路、过程及必要的结果，同时将完整的结果保存到文件“result1_2.xlsx”中，结果保留 3 位小数（例如 1.0%，即 0.010）。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_1  = data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_1['总无机养分百分比'] = data_1[data_1.columns[4:7]].apply(lambda x :format(sum(x),'.3f'),axis=1 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>序号</th>\n",
       "      <th>企业名称</th>\n",
       "      <th>产品通用名称</th>\n",
       "      <th>产品形态</th>\n",
       "      <th>总氮百分比</th>\n",
       "      <th>P2O5百分比</th>\n",
       "      <th>K2O百分比</th>\n",
       "      <th>含氯情况</th>\n",
       "      <th>有机质百分比</th>\n",
       "      <th>正式登记证号</th>\n",
       "      <th>发证日期</th>\n",
       "      <th>有效期</th>\n",
       "      <th>总无机养分百分比</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>安徽中元化肥股份有限公司</td>\n",
       "      <td>复混肥料</td>\n",
       "      <td>颗粒</td>\n",
       "      <td>0.13</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.20</td>\n",
       "      <td>低氯</td>\n",
       "      <td>0.0</td>\n",
       "      <td>皖农肥(2016)准字4255号</td>\n",
       "      <td>2016-01-08</td>\n",
       "      <td>2021-01</td>\n",
       "      <td>0.500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>安徽中元化肥股份有限公司</td>\n",
       "      <td>复混肥料</td>\n",
       "      <td>颗粒</td>\n",
       "      <td>0.13</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.20</td>\n",
       "      <td>中氯</td>\n",
       "      <td>0.0</td>\n",
       "      <td>皖农肥(2016)准字4256号</td>\n",
       "      <td>2016-01-08</td>\n",
       "      <td>2021-01</td>\n",
       "      <td>0.500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>安徽中元化肥股份有限公司</td>\n",
       "      <td>复混肥料</td>\n",
       "      <td>颗粒</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.16</td>\n",
       "      <td>低氯</td>\n",
       "      <td>0.0</td>\n",
       "      <td>皖农肥(2016)准字4257号</td>\n",
       "      <td>2016-01-08</td>\n",
       "      <td>2021-01</td>\n",
       "      <td>0.510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>安徽中元化肥股份有限公司</td>\n",
       "      <td>复混肥料</td>\n",
       "      <td>颗粒</td>\n",
       "      <td>0.26</td>\n",
       "      <td>0.13</td>\n",
       "      <td>0.12</td>\n",
       "      <td>低氯</td>\n",
       "      <td>0.0</td>\n",
       "      <td>皖农肥(2016)准字4258号</td>\n",
       "      <td>2016-01-08</td>\n",
       "      <td>2021-01</td>\n",
       "      <td>0.510</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>安徽中元化肥股份有限公司</td>\n",
       "      <td>复混肥料</td>\n",
       "      <td>颗粒</td>\n",
       "      <td>0.26</td>\n",
       "      <td>0.13</td>\n",
       "      <td>0.12</td>\n",
       "      <td>中氯</td>\n",
       "      <td>0.0</td>\n",
       "      <td>皖农肥(2016)准字4259号</td>\n",
       "      <td>2016-01-08</td>\n",
       "      <td>2021-01</td>\n",
       "      <td>0.510</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   序号          企业名称 产品通用名称 产品形态  总氮百分比  P2O5百分比  K2O百分比 含氯情况  有机质百分比  \\\n",
       "0   1  安徽中元化肥股份有限公司   复混肥料   颗粒   0.13     0.17    0.20   低氯     0.0   \n",
       "1   2  安徽中元化肥股份有限公司   复混肥料   颗粒   0.13     0.17    0.20   中氯     0.0   \n",
       "2   3  安徽中元化肥股份有限公司   复混肥料   颗粒   0.20     0.15    0.16   低氯     0.0   \n",
       "3   4  安徽中元化肥股份有限公司   复混肥料   颗粒   0.26     0.13    0.12   低氯     0.0   \n",
       "4   5  安徽中元化肥股份有限公司   复混肥料   颗粒   0.26     0.13    0.12   中氯     0.0   \n",
       "\n",
       "             正式登记证号        发证日期      有效期 总无机养分百分比  \n",
       "0  皖农肥(2016)准字4255号  2016-01-08  2021-01    0.500  \n",
       "1  皖农肥(2016)准字4256号  2016-01-08  2021-01    0.500  \n",
       "2  皖农肥(2016)准字4257号  2016-01-08  2021-01    0.510  \n",
       "3  皖农肥(2016)准字4258号  2016-01-08  2021-01    0.510  \n",
       "4  皖农肥(2016)准字4259号  2016-01-08  2021-01    0.510  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_1.to_excel('data/result1_2.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
